﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace OEMS
{
   public class GegevensDB
    {
       /// <summary>
       /// Gegevens toeveogen aan database
       /// </summary>
       /// <param name="geg"></param>
       ///<author>Robin Goris</author>
       public static void AddGegevens(Gegevens geg)
       {
           SqlConnection connection = OemsDB.GetConnection();
           SqlTransaction insertTransaction = null;
           string insertStatement = "INSERT Gegevens " +
            "( Beschrijving ) " +
            "VALUES (  @Beschrijving ) ";
           SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
           
           insertCommand.Parameters.AddWithValue("@Beschrijving", geg.beschrijving);

           try
           {
               connection.Open();
               insertTransaction = connection.BeginTransaction();
               insertCommand.Connection = connection;
               insertCommand.Transaction = insertTransaction;

               insertCommand.ExecuteNonQuery();
               insertTransaction.Commit();
           }

           catch (SqlException ex)
           {
               if (insertTransaction != null)
                   insertTransaction.Rollback();
               throw ex;
           }
           finally
           {
               connection.Close();
           }

       }
       /// <summary>
       /// Lijst van gegevens ophalen
       /// </summary>
       /// <param name="geg"></param>
       ///<author>Robin Goris</author>
       public static List<Gegevens> GetGegevens()
       {
           List<Gegevens> gegevensList = new List<Gegevens>();
           SqlConnection connection = OemsDB.GetConnection();
           string selectStatement =
               "SELECT * FROM Gegevens ";
           SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

           try
           {
               connection.Open();
               SqlDataReader reader = selectCommand.ExecuteReader();
               while (reader.Read())
               {
                   Gegevens geg = new Gegevens();
                   geg.gegevensId = (int)reader["GegevensID"];
                   geg.beschrijving = reader["Beschrijving"].ToString();
                   gegevensList.Add(geg);
               }
           }
           catch (SqlException ex)
           {
               throw ex;
           }
           finally
           {
               connection.Close();
           }

           return gegevensList;
       }
      



    }
}
